import sqlite3
import datetime

conn = sqlite3.connect("database\\api_count.db")
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS api_record(
id INTEGER PRIMARY KEY AUTOINCREMENT,
api_name varchar(100),
record_create_time varchar(50),
record_update_time varchar(20)
);
''')

cursor.execute("""
CREATE TABLE IF NOT EXISTS api_record_count(
id INTEGER PRIMARY KEY AUTOINCREMENT,
api_name varchar(100),
count varchar(100),
record_create_time varchar(50),
record_update_time varchar(20),
record_tense varchar(20)
);
""")
cursor.close()
conn.close()


def query_data(api_name):
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute(
        """
    select api_name from api_record_count; 
    """
    )
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    for row in result:
        if api_name in row:
            return True
        else:
            return False


def insert_data(api_name):
    timer = str(datetime.datetime.now())
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute(
        """
insert into api_record(api_name,record_create_time,record_update_time) values("{}","{}","{}");
    """
        .format(api_name, timer, timer))
    cursor.execute("COMMIT;")
    result = cursor.fetchall()
    cursor.close()
    conn.close()


def insert_count_data(api_name):
    timer = str(datetime.datetime.now())
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute(
        """
insert into api_record_count(api_name,count,record_create_time,record_update_time) values("{}","1","{}","{}");
    """
        .format(api_name, timer, timer, "recoding"))
    cursor.execute("COMMIT;")
    result = cursor.fetchall()
    cursor.close()
    conn.close()


def query_record_update_time_data(api_name):
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute(
        """
    select record_update_time from api_record_count where api_name="{}"  order by record_update_time desc limit 1;
    """
        .format(api_name))
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    for row in result:
        return row[0]


#更新统计次数
def update_record_count_data(api_name, new_time_data):
    count = int(get_count_data(api_name)) + 1
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    sql = """"""
    cursor.execute(
        """update api_record_count set count="{}" and record_update_time="{}" where api_name=(select api_name from 
        api_record_count where api_name="{}" order by record_update_time desc limit 1);"""
        .format(count, new_time_data, api_name))

    cursor.execute("COMMIT")
    cursor.close()
    conn.close()


#查询当前接口最新1条数据接口数量
def get_count_data(api_name):
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute(
        """
    select count from api_record_count where api_name="{}"  order by record_update_time desc limit 1;
    """
        .format(api_name))
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    for row in result:
        return row[0]


def update_tense(api_name):
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute(
        """
        update api_record_count set record_tense="{}" where api_name="{}" order by record_update_time desc limit 1;
        """.format("over", api_name)
    )
    cursor.execute("COMMIT;")
    cursor.close()
    conn.close()


def init_db():
    conn = sqlite3.connect("database\\api_count.db")
    cursor = conn.cursor()
    cursor.execute("delete from api_record;")
    cursor.execute("COMMIT;")
    cursor.execute("DELETE FROM api_record_count;")
    cursor.execute("COMMIT;")
    cursor.close()
    conn.close()
